 ALTER                                     proc sp_customer_report
  
  -- Input parameter is process StepID;
  @Product_ID 		varchar(15),
  @Sales_Order_Number 	varchar(15),
  @Customer_ID 		varchar(15),
  @Sales_Person 	varchar(50),
  @asOfDate 		varchar(15),
  @Backlog_Type 	varchar(10),
  @defaultPrompt 	varchar(20),
  @Category		varchar(100),
  @order_type 		int 
  
  as
  
  /*variable to store the values
  */
   -- End_User field added into results table by gandhi on 13th Sep 2004
  declare @date_in datetime, @date_out datetime
  declare @results Table(Customer_Name varchar(65),Customer_Number varchar(50), Sales_Order_Number varchar(50),Customer_PO varchar(50), 
  SO_Date datetime, Sales_Person varchar(50), End_User varchar(50), Sales_Rep varchar(50), Sales_Hold varchar(10), Marketing_Hold varchar(10),Manufacturing_Hold varchar(10), 
  Finance_Hold varchar(10), Req_Date datetime, Schd_Date datetime, Rev_Date datetime, Part_Number varchar(50), Req_Qyt int, Rem_Qty int ,
  Unit_Price float, Status varchar(20), LineItem_No int,RevLevel int, BacklogType varchar(20), BookingMonth int, BookingYear int, BookingQuarter int, QuarterEnd int,ActYear int,FinYear int,Is_Expedite int)
  
  declare @booking_date  datetime -- Added by Basha (11/21/2003)
  declare @so_BK_Mt_I int, @so_Bk_Yr_I int, @so_Bk_Qt_I int, @so_Bk_Qt_End_I int -- Added by Basha (11/21/2003)
  declare @act_year int , @fin_year int -- Added By Basha (11/23/2003)
  
  declare @tempDate datetime
 
   if @asOfDate = convert(varchar, CURRENT_TIMESTAMP, 101)
  begin
  	set @asOfDate='0'
  end
 
  if @asOfDate <> '0' 
  begin
 	set @tempDate = convert(datetime, @asOfDate)
  	exec sp_backlog_reports_as_of_date @Product_ID, @Sales_Order_Number, @Customer_ID, @Sales_Person, @tempDate, @Backlog_Type, @Category ,@order_type
  end
  else
  begin
 
  
  if @Backlog_Type = 'all'
  begin
  	set @backlog_Type = '0'
  end
  
  declare @lineItemCursor CURSOR
  
  if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person = '0')
  begin
       set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost, cool.COOL_Status ,cool.COOL_Is_Expedite
  	from 	customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = 
  		customer.customer_id and cool.COOL_Status<> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type  
  end
  
  else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person = '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from 	customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.product_id = 
  		@product_id and cool.COOL_Status<> 'SHIPPED' and  sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID ='0' and @Sales_Person = '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from	customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 		sales_order.salesOrder_no = @sales_order_number and cool.COOL_Status <> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type
  end
  
  else if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person = '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from	customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.customer_id = @customer_id and cool.COOL_Status <> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person <> '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.so_sales_person = @sales_person and cool.COOL_Status <> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID ='0' and @Sales_Person = '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.product_id = 
  		@product_id and sales_order.salesOrder_no = @sales_order_number and cool.COOL_Status <> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person = '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date,cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.product_id = 
  		@product_id and sales_order.customer_id = @customer_id and cool.COOL_Status<> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person <> '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.product_id = 
  		@product_id and sales_order.so_sales_person = @sales_person and cool.COOL_Status<> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person = '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.salesorder_no = @sales_order_number and sales_order.customer_id = @customer_id and cool.COOL_Status <> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID ='0' and @Sales_Person <> '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.salesorder_no = @sales_order_number and sales_order.so_sales_person = @sales_person and cool.COOL_Status <> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else  if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.salesorder_no = @sales_order_number and sales_order.so_sales_person = @sales_person and sales_order.customer_id = @customer_id and cool.COOL_Status <> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer where cool.salesorder_ID = 
  		sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.customer_id = @customer_id and sales_order.so_sales_person = @sales_person and cool.COOL_Status<> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.salesorder_no = @sales_order_number and sales_order.so_sales_person = @sales_person and 
  		sales_order.customer_id = @customer_id and cool.product_id = @product_id and cool.COOL_Status <> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  --Modified By Lavanya ( 05-19-2004 )
  
  /* (Removed the where condition for sales_order.salesorder_no = @sales_order_number
  and added new condition for Part Number,Sales Order number,Sales person given and Customer field not given )
  
  else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.salesorder_no = @sales_order_number and sales_order.so_sales_person = @sales_person and sales_order.customer_id = @customer_id and cool.product_id = 
  		@product_id and cool.COOL_Status<> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  */
  else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.so_sales_person = @sales_person and sales_order.customer_id = @customer_id and cool.product_id = 
  		@product_id and cool.COOL_Status<> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID = '0' and @Sales_Person <> '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.so_sales_person = @sales_person and sales_order.salesorder_no = @sales_order_number and cool.product_id = 
  		@product_id and cool.COOL_Status<> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end 
  
  --End of Modification By lavanya
  
  
  else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person = '0')
  begin
  	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
  		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.so_EndUser, sales_order.Regional_Rep, 
  		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
  		cool.cool_unit_cost,cool.COOL_Status ,cool.COOL_Is_Expedite
  	from customer_open_Order_lineItem cool, product, sales_order, customer 
  	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
  		sales_order.salesorder_no = @sales_order_number and 
  		sales_order.customer_id = @customer_id and cool.product_id = @product_id and cool.COOL_Status <> 'SHIPPED' and sales_order.SO_Is_Sample = @order_type 
  end
  
  declare @customer_Name varchar (65), @customer_number varchar (50), @so_no varchar (50)
  declare @line_item_id int, @customer_po varchar (50), @so_date datetime, @s_person varchar(50), @End_User varchar(50) 
  declare @sales_rep varchar(50), @req_date datetime, @part_number varchar(50), @quantity int
  declare @unit_cost float, @sch_date datetime, @rev_date datetime,@status varchar (20), @line_item_no int
  declare @COOL_Is_Cancel int, @sales_hold varchar(50), @mktg_hold varchar(50), @mfg_hold varchar(50)
  declare @fin_hold varchar(50), @rev_level int, @rev_so_id int
  
  declare @is_expedite int 
  declare @quarter_cal_date datetime -- Added by Basha (11/23/2004
  
  open @lineItemCursor 
  fetch next from @lineItemCursor into @line_item_id,@customer_Name,@customer_number,@so_no,@customer_po,@so_date, 
  		@s_person, @End_User, @sales_rep,  @req_date,@sch_date,@rev_date,@part_number, @quantity, @unit_cost, @status , @is_expedite
  
  	while @@fetch_status = 0
  	begin
  		
  		-- Begin : Added by Basha (11/21/2003)
  		
  		if (@Category = 'reqDockDate')
  		begin
  			set @quarter_cal_date = @req_date
  		end
  		else if (@Category = 'ScheduleDate')
  		begin
  			set @quarter_cal_date = @sch_date
  		end
  		else if (@Category = 'revDate')
  		begin
  			if ( @rev_date is not null)	
  			begin
  				set @quarter_cal_date = @rev_date
  			end
  			else
  			begin
  				set @quarter_cal_date = @sch_date
  				set @rev_date = @sch_date
  			end
  		end
  		else if (@Category = 'salesOrder' or @Category = 'partNumber')
  		begin
  			set @quarter_cal_date = @so_date
  		end
  
  
  		if( @quarter_cal_date is not null)
  		begin
  			set @so_Bk_Yr_I = DATEPART(yyyy, @quarter_cal_date)
  			set @act_year = @so_Bk_Yr_I
  			set @so_BK_Mt_I = DATEPART(m, @quarter_cal_date)
  			-- Modified by Prabhanjan (14 Apr 2004)
  			--if (@so_BK_Mt_I >=2 and @so_BK_Mt_I <=4)
  			if (@so_BK_Mt_I >=1 and @so_BK_Mt_I <=3)
  			begin
  				set @so_Bk_Qt_I = 1
  				--set @so_Bk_Qt_End_I =4
  				set @so_Bk_Qt_End_I =3
  
  				set @fin_year = @so_Bk_Yr_I
  			end
  			--else if (@so_BK_Mt_I >=5 and @so_BK_Mt_I <=7)
  			else if (@so_BK_Mt_I >=4 and @so_BK_Mt_I <=6)			begin
  				set @so_Bk_Qt_I = 2
  				--set @so_Bk_Qt_End_I =7
  				set @so_Bk_Qt_End_I =6
  
  				set @fin_year = @so_Bk_Yr_I
  			end
  			--else if (@so_BK_Mt_I >=8 and @so_BK_Mt_I <=10)
  			else if (@so_BK_Mt_I >=7 and @so_BK_Mt_I <=9)
  			begin
  				set @so_Bk_Qt_I = 3
  				--set @so_Bk_Qt_End_I =10
  				set @so_Bk_Qt_End_I =9
  
  				set @fin_year = @so_Bk_Yr_I
  			end						
  			--else if ((@so_BK_Mt_I =1) or (@so_BK_Mt_I >=11 and @so_BK_Mt_I <=12))
  			else if (@so_BK_Mt_I >=10 and @so_BK_Mt_I <=12)
  			begin
  				set @so_Bk_Qt_I = 4
  				--set @so_Bk_Qt_End_I =1
  				set @so_Bk_Qt_End_I =12
 				
  				set @fin_year = @so_Bk_Yr_I
  				/*
  				if (@so_BK_Mt_I = 1) -- Added by Basha (11/23/2003)
  					set @fin_year = @so_Bk_Yr_I - 1
  
  				if @so_Bk_Mt_I = 12 or @so_Bk_Mt_I = 11 
  				begin
  					set @fin_year = @so_Bk_Yr_I
  					set @so_Bk_Yr_I = @so_Bk_Yr_I+1
  				end
  				*/
  			
  			end
  
  		end
  		else
  		begin
  			set @act_year = null
  			set @fin_year = null
  
  			set @so_BK_Mt_I = 0
  			set @so_Bk_Yr_I = null
  			set @so_Bk_Qt_I = 0
  			set @so_Bk_Qt_End_I = 0
  		end
  		-- End : Added by Basha (11/21/2003)		
  
  
  		select @rev_so_id = salesorder_id from sales_order where salesorder_no = @so_no and sales_order.SO_Is_Sample = @order_type 
  		select @rev_level = max(ChangeNo) from change_sales_order where salesorder_id = @rev_so_id and change_sales_order.CSO_Is_Sample = @order_type 
  
  		if @rev_level is null
  			set @rev_level = 0
  
  		if @status = 'APPROVED'
  		begin
  			
  			declare @temp_status varchar (20) -- , @temp_ps varchar (20)
  
  			
  			-- Condition Modified By Basha (11/13/2003)
  			
  			declare temp_status_cursor cursor for 
  			select distinct CST.CST_Status --,  CST.Label 
  			from 	customer_shipping_table CST, 
  				customer_open_order_lineitem COOL,
  				customer_shipping_line_item_table cslit
  			where COOL.LineItem_ID = @line_item_id and cool.LineItem_ID = cslit. LineItem_ID and cslit.customershipping_id = cst.customershipping_id and cslit.cslit_status = 'SHIPPED' -- cslit.cslit_status <> 'CANCELLED'
  
  			
  			set @temp_status = null -- Added by Basha (11/13/2003)
  
  			open temp_status_cursor
  			fetch next from temp_status_cursor into @temp_status --, @temp_ps
  
  
  			-- Begin : @temp_status is null condition	
  			if @temp_status is null
  			begin
  				select @line_item_no = COOL_Line_Item_No, @temp_status=cool_status 
  				from customer_open_order_lineitem 
  				where LineItem_ID = @line_item_id 
  
  				--the status had to be set to cancelled if the COOL_Is_Cancel is set to 0
  
  				select @COOL_Is_Cancel=COOL_Is_Cancel 
  				from customer_open_order_lineitem 
  				where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and sales_order.SO_Is_Sample = @order_type ) and cool_line_item_no=@line_item_no
  
  				--select COOL_Is_Cancel from customer_open_order_lineitem where salesorder_id = (select salesorder_id from sales_order where salesorder_no = 10382 and sales_order.SO_Is_Sample = @order_type ) and cool_line_item_no=1
  
  				if @COOL_Is_Cancel = 0
  				begin
  					set @temp_status= 'CANCELLED'
  					
  				end  
  				insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, End_User, Sales_Rep, 
  					Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, 
  					LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd , ActYear,FinYear,Is_Expedite)
  				values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@End_User,@sales_rep,@sales_hold, 
  					@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number, @quantity, @quantity, @unit_cost, 
  					@temp_status,@line_item_no,@rev_level, 'Open', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year,@is_expedite) 
  
  				set @line_item_id = null
  				set @temp_status = null
  				set @rev_level = null
  				set @rev_so_id = null
  				set @COOL_Is_Cancel=null
  
  				--there are some nulls now. We dont want the nulls to be repeated 
  
  				set @rev_date = null
  			end
  			-- End : @temp_status is null condition
  	
  			while @@fetch_status = 0
  			begin
  				if @temp_status is not null 
  				begin
  					select @line_item_no = COOL_Line_Item_No 
  					from customer_open_order_lineitem where LineItem_ID = @line_item_id 
  
  					if @temp_status is not null
  					begin
                                                  /* Commented By Basha (11/12/2003)
  						declare statusCursor cursor for
  						select distinct CST.CST_Status -- Modified by Basha (11/13/2003)
  						from customer_shipping_table CST, customer_open_order_lineitem COOL
  						where COOL.SalesOrder_ID=CST.SalesOrder_ID and COOL.LineItem_ID = @line_item_id and CST.CST_Status <> 'CANCELLED' -- and cst.Label = @temp_ps
  	
  						open statusCursor
  						fetch next from statusCursor into @temp_status
  	
  						--  Begin : @@fetch_status = 0 for statusCursor
  						while @@fetch_status = 0
  						begin
  						*/
  							-- Begin : @temp_status <> 'SHIPPED'
  							
  							if @temp_status <> 'SHIPPED'
  							begin 
  								set @rev_level = 0
  										
  								select @COOL_Is_Cancel=COOL_Is_Cancel 
  								from customer_open_order_lineitem 
  								where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and sales_order.SO_Is_Sample = @order_type ) and cool_line_item_no=@line_item_no
  	
  								if @COOL_Is_Cancel = 0
  								begin
  									set @temp_status= 'CANCELLED'
  
  								end
  
  								declare @ship_qty int
  	
  								select @ship_qty = isNUll(sum(cslit_qty_shipped),0)
  								from customer_shipping_line_item_table 
  								where customer_shipping_line_item_table.lineitem_id = @line_item_id  and  customer_shipping_line_item_table.cslit_status = 'SHIPPED' 
  	
  								set @ship_qty  = @quantity - @ship_qty 
  								
  								insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, End_User, Sales_Rep, 
  									Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, 
  									Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, 
  									LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear,Is_Expedite)
  								values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@End_User,@sales_rep,@sales_hold, 
  									@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, 
  									@part_number,@quantity , @ship_qty, @unit_cost, @temp_status,@line_item_no,@rev_level, 'Open', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year,@is_expedite) 
  								
  	
  								set @rev_level = null
  								set @rev_so_id = null
  								set @COOL_Is_Cancel=null
  	
  							end
  							-- End : @temp_status <> 'SHIPPED'
  	
  							-- Begin : @temp_status = 'SHIPPED'
  							else if @temp_status = 'SHIPPED'
  							begin
  							
  								--declare @ship_qty int --Commented By Basha (11/13/2003)
  								
  								select @ship_qty = isNull(sum(cslit_qty_shipped) , 0)
  								from customer_shipping_line_item_table 
  								where customer_shipping_line_item_table.lineitem_id = @line_item_id  and customer_shipping_line_item_table.cslit_status = 'SHIPPED' 
  	
  								set @ship_qty  = @quantity - @ship_qty 
  
  								/* Commented by Basha (11/13/2003)
  								if @ship_qty is null
  								begin
  									set @ship_qty = 0
  								end
  								*/
  				
  													
  								if (@ship_qty  > 0)
  								begin
  									select @COOL_Is_Cancel=COOL_Is_Cancel 
  									from customer_open_order_lineitem 
  									where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and sales_order.SO_Is_Sample = @order_type ) and cool_line_item_no=@line_item_no
  	
  									if @COOL_Is_Cancel = 0
  									begin
  										set @temp_status= 'CANCELLED'
  										
  									end
  									
  									insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, End_User, Sales_Rep, 
  										Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, 
  										Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear,Is_Expedite)
  									values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@End_User,@sales_rep,@sales_hold, 
  										@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number, @quantity, @ship_qty, @unit_cost,@status,@line_item_no,@rev_level, 'Open', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year,@is_expedite) 
  	
  									set @rev_level = null
  									set @rev_so_id = null
  									set @COOL_Is_Cancel = null
  	        							
  								end
  													
  							end
  							-- End : @temp_status = 'SHIPPED'
  							
  						/* Commented By Basha (11/13/2003)
  							fetch next from statusCursor into @temp_status
  						end
  						--  End : @@fetch_status = 0 for statusCursor 
  					
  						close statusCursor
  						deallocate statusCursor
  						*/
  					end
  					--  End : @temp_status is not null
  				
  					else
  					begin
  						select @COOL_Is_Cancel=COOL_Is_Cancel 
  						from customer_open_order_lineitem where salesorder_id = (select salesorder_id from sales_order 
  						where salesorder_no = @so_no and sales_order.SO_Is_Sample = @order_type ) and cool_line_item_no=@line_item_no
  	
  						if @COOL_Is_Cancel = 0
  						begin
  							set @temp_status= 'CANCELLED'
  							
  						end
  						
  						insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, End_User, Sales_Rep, Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear,Is_Expedite)
  						values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@End_User,@sales_rep,@sales_hold, 
  							@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number, @quantity, @quantity, @unit_cost, @temp_status,@line_item_no,@rev_level,'Open', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year,@is_expedite) 
  						
  
  						set @rev_level = null
  						set @rev_so_id = null
  						set @COOL_Is_Cancel = null
  	
  					end
  				end
  				--  End : @temp_status is not null
  			
  				fetch next from temp_status_cursor into @temp_status --, @temp_ps
  
  				-- Commented By Basha (11/13/2003)
  				-- set @temp_status= NULL
  				-- set @line_item_id = NULL
  
  			end
  			-- End : @@fetch_status = 0 for temp_status_cursor
  
  			close temp_status_cursor
  			deallocate temp_status_cursor
                                     
  		end 
  		-- End : APPROVED
  		else
  		begin
  			
  			select @sales_hold = hold_reason_code from sales_hold_table where hold_by_group = 'SALES' and lineitem_id = @line_item_id
  			select @mktg_hold = hold_reason_code from sales_hold_table where hold_by_group = 'MARKETING' and lineitem_id = @line_item_id
  			select @mfg_hold = hold_reason_code from sales_hold_table where hold_by_group = 'OPERATIONS' and lineitem_id = @line_item_id
  			select @fin_hold = hold_reason_code from sales_hold_table where hold_by_group = 'FINANCE' and lineitem_id = @line_item_id
  
  			if @sales_hold = '0' set @sales_hold = 'X' 
  			if @mktg_hold = '0'  set @mktg_hold = 'X' 
  			if @mfg_hold = '0'   set @mfg_hold = 'X' 
  			if @fin_hold = '0'    set @fin_hold = 'X' 
  
  			declare @shipped_qty int, @lineitem_id int
    
  			select @lineitem_id = lineitem_id from customer_shipping_line_item_table where lineitem_id = @line_item_id and cslit_status = '_SHIPPED_' 
    
    			if (@lineitem_id is not null)
  			begin
  				select @shipped_qty = sum(cslit_qty_shipped) from customer_shipping_line_item_table 
  				where customer_shipping_line_item_table.lineitem_id = @line_item_id  and customer_shipping_line_item_table.cslit_status = '_SHIPPED_' or customer_shipping_line_item_table.cslit_status = 'SHIPPED' 
        
  				set @shipped_qty = @quantity-@shipped_qty
  				if (@shipped_qty > 0)
  				begin
  					select @line_item_no = COOL_Line_Item_No from customer_open_order_lineitem where LineItem_ID = @line_item_id  
  
  					select @COOL_Is_Cancel=COOL_Is_Cancel from customer_open_order_lineitem 
  					where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and sales_order.SO_Is_Sample = @order_type ) and cool_line_item_no=@line_item_no
  
  					if @COOL_Is_Cancel = 0
  					begin
  						set @temp_status= 'CANCELLED'
  						
  					end
  					
  					
  					insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, 
  						SO_Date, Sales_Person, End_User, Sales_Rep, Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, 
  						rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear,Is_Expedite)
  					values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@End_User,@sales_rep,@sales_hold, 
  						@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number,@quantity, @shipped_qty, @unit_cost,@status, @line_item_no,@rev_level, 'Hold', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year,@is_expedite) 
  
  					
  					select @rev_level = null, @rev_so_id = null, @COOL_Is_Cancel = null
  
  				end
  			end
         			else 
  			begin
  				select @line_item_no = COOL_Line_Item_No from customer_open_order_lineitem where LineItem_ID = @line_item_id 
  
  				select @COOL_Is_Cancel = COOL_Is_Cancel from customer_open_order_lineitem 
  				where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and sales_order.SO_Is_Sample = @order_type ) and cool_line_item_no = @line_item_no
  
  				set @ship_qty = 0 -- Added by Basha (11/18/2003)
  				if @COOL_Is_Cancel = 0
  				begin
  					set @status= 'CANCELLED'
  					
  					-- Added By Basha (11/13/2003)
  					select @ship_qty = isNUll(sum(cslit_qty_shipped),0)
  					from customer_shipping_line_item_table 
  					where customer_shipping_line_item_table.lineitem_id = @line_item_id  and customer_shipping_line_item_table.cslit_status = 'SHIPPED' 
  				
  				end
  
  				set @ship_qty  = @quantity - @ship_qty -- Moved from if statement
  				
  
  				insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, End_User, Sales_Rep, 
  					Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status,LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear,Is_Expedite)
  				values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@End_User,@sales_rep,@sales_hold, 
  					@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number,@quantity, @ship_qty, @unit_cost,@status,@line_item_no,@rev_level, 'Hold', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year,@is_expedite) 
  				
  
  
  				-- select @rev_level = null, @rev_so_id = null, @COOL_Is_Cancel = null
    
  			end
  			
  
  
  			select @customer_name = NULL, @customer_number = NULL,  @so_no = NULL, @line_item_id = NULL
  			select @customer_po = NULL, @so_date = NULL, @s_person = NULL, @sales_rep  = NULL, @req_date =NULL
  			select @part_number = NULL,@quantity = NULL, @unit_cost = NULL, @sales_hold = NULL, @mktg_hold  = NULL
  			select @mfg_hold = NULL, @fin_hold = NULL, @sch_date = NULL, @shipped_qty = NULL, @lineitem_id = NULL
  			select @line_item_no = NULL, @COOL_Is_Cancel = null, @rev_date = null
  		end
  
  		-- End : Not APPROVED
  
  		set @so_date = null -- Added by Basha (11/21/2003)
  
  		fetch next from @lineItemCursor into  @line_item_id,@customer_Name,@customer_number,@so_no,@customer_po,@so_date, 
  			@s_person, @End_User, @sales_rep,  @req_date,@sch_date,@rev_date, @part_number, @quantity, @unit_cost, @status,@is_expedite
  	end
  	
  	-- End : @@fetch_Status = 0 for lineItemCursor
  
  	close @lineItemCursor
  	deallocate @lineItemCursor
  
  	
  	if @Backlog_Type <> '0' 
  	begin
  		
  		--select * from @results where BacklogType = @Backlog_Type
  		--select * from @results where BacklogType = @Backlog_Type order by So_Date , BookingYear , BookingQuarter 
  		if (@Category = 'reqDockDate')		begin
  			select * from @results where BacklogType = @Backlog_Type order by Req_Date
  		end
  		else if (@Category = 'ScheduleDate')
  		begin
  			select * from @results where BacklogType = @Backlog_Type order by Schd_Date
  		end
  		else if (@Category = 'revDate')
  		begin
  		--print @Backlog_Type
  			select * from @results where BacklogType = @Backlog_Type order by Rev_Date
  		end
  		else if (@Category = 'salesOrder')
  		begin
  			select * from @results where BacklogType = @Backlog_Type order by Sales_Order_Number
  		end
  		else if (@Category = 'partNumber')
  		begin
  			select * from @results where BacklogType = @Backlog_Type order by Part_Number
  		end
  	end
  	else
  	begin
  		--select * from @results 
  		--select * from @results order by So_Date , BookingYear , BookingQuarter
  		if (@Category = 'reqDockDate')
  		begin
  			select * from @results order by Req_Date
  		end
  		else if (@Category = 'ScheduleDate')
  		begin
  			select * from @results order by Schd_Date
  		end
  		else if (@Category = 'revDate')
  		begin
  			select * from @results order by Rev_Date
  		end
  		else if (@Category = 'salesOrder')
  		begin
  			select * from @results order by Sales_Order_Number
  		end
  		else if (@Category = 'partNumber')
  		begin
  			select * from @results order by Part_Number
  		end
  	end
  end
